package com.xingyi.bacs.dao;

import com.xingyi.bacs.bean.Bill;
import com.xingyi.bacs.bean.Merchant;
import com.xingyi.bacs.bean.Settlement;
import com.xingyi.bacs.util.JDBCUtil;
import com.xingyi.bacs.util.LogUtil;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

@Repository
public class QsDaoImpl implements QsDao{


    @Override
    public void insertMerchantTemp(List<Merchant> list) {
        long startTime = System.currentTimeMillis();
        Connection conn = null;
        PreparedStatement pstat = null;
        Merchant obj = null;
        String sql = "insert into T_RUN_MERCHANT_TEMP (" +
                "        id," +
                "        ACCOUNT_AREA," +
                "        ACCOUNT_NAME," +
                "        ACCOUNT_NATURE," +
                "        ACCOUNT_TYPE," +
                "        AGENT_CODE," +
                "        AGENT_NAME," +
                "        BANK_NAME," +
                "        BANK_NUMBER," +
                "        BUSINESS_ADDRESS," +
                "        BUSINESS_CODE," +
                "        BUSINESS_NAME," +
                "        CARD_NUMBER," +
                "        CHANNELID," +
                "        CITIES," +
                "        CREATEDATE," +
                "        IDENTITY," +
                "        IMPORT_BATCHNO," +
                "        MERCHANT_CODE," +
                "        MERCHANT_NAME," +
                "        OPEN_PROJECT," +
                "        ORGANIZATION_CODE," +
                "        ORGANIZATION_NAME," +
                "        POSTAL_SIGN," +
                "        PROVINCES," +
                "        SIGNING_BANK," +
                "        STOPPAYMENTSIGN," +
                "        UPDATETIME," +
                "        VALIDSIGN," +
                "        CHECK_DATETIME," +
                "        CHECK_USERID," +
                "        CHECK_USERNAME," +
                "        CHANNEL_NAME," +
                "        channelid1," +
                "        channel_name1," +
                "        channelid2," +
                "        channel_name2," +
                "        channelid3," +
                "        channel_name3," +
                "        channelid4," +
                "        channel_name4," +
                "        phoneNumber" +
                "        ) VALUES " +
                "        (?,?,?,?,?,?,?,?,?,?,?," +
                "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," +
                "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            conn = JDBCUtil.getQSConn();
            pstat = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            for(int i=0;i<list.size();i++){
                obj = list.get(i);
                pstat.setInt(1, obj.getId());
                pstat.setString(2, obj.getAccountArea());
                pstat.setString(3, obj.getAccountName());
                pstat.setInt(4, obj.getAccountNature());
                pstat.setInt(5, obj.getAccountType());
                pstat.setString(6, obj.getAgentCode());
                pstat.setString(7, obj.getAgentName());
                pstat.setString(8, obj.getBankName());
                pstat.setString(9, obj.getBankNumber());
                pstat.setString(10, obj.getBusinessAddress());
                pstat.setString(11, obj.getBusinessCode());
                pstat.setString(12, obj.getBusinessName());
                pstat.setString(13, obj.getCardNumber());
                pstat.setInt(14, obj.getChannelId());
                pstat.setString(15, obj.getCities());
                pstat.setString(16, obj.getCreateDate());
                pstat.setString(17, obj.getIdentity());
                pstat.setString(18, obj.getImportBatchNo());
                pstat.setString(19, obj.getMerchantCode());
                pstat.setString(20, obj.getMerchantName());
                pstat.setString(21, obj.getOpenProject());
                pstat.setString(22, obj.getOrganizationCode());
                pstat.setString(23, obj.getOrganizationName());
                pstat.setInt(24, obj.getPostalSign());
                pstat.setString(25, obj.getProvinces());
                pstat.setString(26, obj.getSigningBank());
                pstat.setInt(27, obj.getStopPaymentSign());
                pstat.setString(28, obj.getUpdateTime());
                pstat.setInt(29, obj.getValidSign());
                pstat.setString(30, obj.getCheckDateTime());
                pstat.setInt(31, obj.getCheckUserId());
                pstat.setString(32, obj.getCheckUserName());
                pstat.setString(33, obj.getChannelName());
                pstat.setInt(34, obj.getChannelId1());
                pstat.setString(35, obj.getChannelName1());
                pstat.setInt(36, obj.getChannelId2());
                pstat.setString(37, obj.getChannelName2());
                pstat.setInt(38, obj.getChannelId3());
                pstat.setString(39, obj.getChannelName3());
                pstat.setInt(40, obj.getChannelId4());
                pstat.setString(41, obj.getChannelName4());
                pstat.setString(42, obj.getPhoneNumber());
                pstat.addBatch();
                if (i%2000==0&&i>0) {
                    pstat.executeBatch();
                    conn.commit();
                    LogUtil.info(this.getClass(), "商户临时表分批保存条数至："+i);
                }
            }
            pstat.executeBatch();
            conn.commit();
            long endTime = System.currentTimeMillis();
            LogUtil.info(this.getClass(), "商户临时表分批保存条数至："+list.size()+"，耗时："+(endTime-startTime)/1000);
        } catch (Exception e) {
            e.printStackTrace();
            if(conn!=null){
                try {
                    conn.rollback();
                    LogUtil.info(this.getClass(), "商户临时表分批次保存异常数据进行回滚！");
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            throw new RuntimeException(e.getMessage());
        } finally {
            JDBCUtil.getClose(conn, pstat, null);
        }
    }

    @Override
    public void insertSettlement(List<Settlement> list) {
        long startTime = System.currentTimeMillis();
        Connection conn = null;
        PreparedStatement pstat = null;
        Settlement obj = null;
        String sql = "insert into T_RUN_SETTLEMENT_DATA (" +
                "        id," +
                "        settlment_batchNum," +
                "        liquidation_date," +
                "        merchant_code," +
                "        organization_code," +
                "        organization_name," +
                "        business_code," +
                "        business_name," +
                "        project_code," +
                "        project_name," +
                "        card_number," +
                "        account_name," +
                "        settlement_money," +
                "        settlement_count," +
                "        merge_user_id," +
                "        DATA_STATU," +
                "        BEPAY_BATCH," +
                "        CHANNEL_ID," +
                "        CHANNEL_STATU," +
                "        SETTLEMENT_STATU," +
                "        SERIA_NUMBER," +
                "        IDENTITY," +
                "        PAY_RESPONSE_CODE," +
                "        LAST_UPDATE_TIME," +
                "        IS_CREATE_NEW_BEPAYBATCH," +
                "        FAIL_COUNT," +
                "        bank_name," +
                "        account_type," +
                "        account_nature," +
                "        signing_bank," +
                "        account_area," +
                "        DEFAULT_CHANNEL_ID," +
                "        OLD_RESPONSE_CODE," +
                "        OLD_SETTLEMENT_STATU," +
                "        UPLOAD_TIME," +
                "        DATA_FROM," +
                "        CHANNEL_FLAG," +
                "        IMPORT_BATCHNO," +
                "        ROW_INDEX," +
                "        OLD_LIQUI_DATE," +
                "        SETTLE_CHANNEL," +
                "        FIRST_LIQUI_DATE," +
                "        postal_sign," +
                "        channel_str," +
                "        channelBlk_str," +
                "        PLAN_CHANNELID," +
                "        PLAN_CHANNELLV," +
                "        BILL_STATUS," +
                "        old_settlment_batchNum," +
                "        RESPITE_STATUS," +
                "        LIQUIDATION_ID," +
                "        merchant_name," +
                "        agent_code," +
                "        agent_name," +
                "        PROFIT_STATUS," +
                "        phoneNumber," +
                "        FYJ_STATUS," +
                "        POUNDAGE_MONEY," +
                "        YLT1CHANNEL_STATUS," +
                "        MERCHANT_POSTSCRIPT," +
                "        FILE_FROM_NAME," +
                "        CLEAR_MODE" +
                "        ) VALUES " +
                "        (?,?,?,?,?,?,?,?,?,?,?," +
                "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," +
                "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," +
                "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            conn = JDBCUtil.getQSConn();
            pstat = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            for(int i=0;i<list.size();i++){
                obj = list.get(i);
                pstat.setInt(1, obj.getId());
                pstat.setString(2, obj.getSettlmentBatchNum());
                pstat.setString(3, obj.getLiquidationDate());
                pstat.setString(4, obj.getMerchantCode());
                pstat.setString(5, obj.getOrganizationCode());
                pstat.setString(6, obj.getOrganizationName());
                pstat.setString(7, obj.getBusinessCode());
                pstat.setString(8, obj.getBusinessName());
                pstat.setString(9, obj.getProjectCode());
                pstat.setString(10, obj.getProjectName());
                pstat.setString(11, obj.getCardNumber());
                pstat.setString(12, obj.getAccountName());
                pstat.setBigDecimal(13, obj.getSettlementMoney());
                pstat.setInt(14, obj.getSettlementCount());
                pstat.setInt(15, obj.getMergeUserId());
                pstat.setInt(16, obj.getDataStatu());
                pstat.setString(17, obj.getBepayBatch());
                pstat.setInt(18, obj.getChannelId());
                pstat.setInt(19, obj.getChannelStatu());
                pstat.setInt(20, obj.getSettlementStatu());
                pstat.setString(21, obj.getSeriaNumber());
                pstat.setString(22, obj.getIdentity());
                pstat.setString(23, obj.getPayResponseCode());
                pstat.setString(24, obj.getLastUpdateTime());
                pstat.setInt(25, obj.getIsCreateNewBepayBatch());
                pstat.setInt(26, obj.getFailCount());
                pstat.setString(27, obj.getBankName());
                pstat.setInt(28, obj.getAccountType());
                pstat.setInt(29, obj.getAccountNature());
                pstat.setString(30, obj.getSigningBank());
                pstat.setString(31, obj.getAccountArea());
                pstat.setInt(32, obj.getDefaultChannelId());
                pstat.setString(33, obj.getOldResponseCode());
                pstat.setInt(34, obj.getOldSettlementStatu());
                pstat.setString(35, obj.getUploadTime());
                pstat.setInt(36, obj.getDataFrom());
                pstat.setInt(37, obj.getChannelFlag());
                pstat.setString(38, obj.getImportBatchNo());
                pstat.setInt(39, obj.getRowIndex());
                pstat.setString(40, obj.getOldLiquiDate());
                pstat.setInt(41, obj.getSettleChannel());
                pstat.setString(42, obj.getFirstLiquiDate());
                pstat.setInt(43, obj.getPostalSign());
                pstat.setString(44, obj.getChannelIdStr());
                pstat.setString(45, obj.getChannelIdBlkStr());
                pstat.setInt(46, obj.getPlanChannelId());
                pstat.setInt(47, obj.getPlanChannelLevel());
                pstat.setInt(48, obj.getBillStatus());
                pstat.setString(49, obj.getOldSettlmentBatchNum());
                pstat.setInt(50, obj.getRespiteStatus());
                pstat.setInt(51, obj.getLiquidationId());
                pstat.setString(52, obj.getMerchantName());
                pstat.setString(53, obj.getAgentCode());
                pstat.setString(54, obj.getAgentName());
                pstat.setInt(55, obj.getProfitStatus());
                pstat.setString(56, obj.getPhoneNumber());
                pstat.setInt(57, obj.getFyjStatus());
                pstat.setBigDecimal(58, obj.getPoundageMoney());
                pstat.setInt(59, obj.getYlT1ChannelStatus());
                pstat.setString(60, obj.getMerchantPostscript());
                pstat.setString(61, obj.getFileFromName());
                pstat.setString(62, obj.getClearMode());
                pstat.addBatch();
                if (i%2000==0&&i>0) {
                    pstat.executeBatch();
                    conn.commit();
                    LogUtil.info(this.getClass(), "结算表分批保存条数至："+i);
                }
            }
            pstat.executeBatch();
            conn.commit();
            long endTime = System.currentTimeMillis();
            LogUtil.info(this.getClass(), "结算表分批保存条数至："+list.size()+"，耗时："+(endTime-startTime)/1000);
        } catch (Exception e) {
            e.printStackTrace();
            if(conn!=null){
                try {
                    conn.rollback();
                    LogUtil.info(this.getClass(), "结算表分批次保存异常数据进行回滚！");
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            throw new RuntimeException(e.getMessage());
        } finally {
            JDBCUtil.getClose(conn, pstat, null);
        }
    }

    @Override
    public void insertBill(List<Bill> list) {
        long startTime = System.currentTimeMillis();
        Connection conn = null;
        PreparedStatement pstat = null;
        Bill obj = null;
        String sql = "insert into T_RUN_BILL_DATA (" +
                "        id," +
                "        CLEAR_DATE," +
                "        BEPAY_BATCH," +
                "        MERCHANT_CODE," +
                "        MERCHANT_NAME," +
                "        AGENT_CODE," +
                "        AGENT_NAME," +
                "        ACCOUNT_NAME," +
                "        CARD_NUMBER," +
                "        BANK_NAME," +
                "        ACCOUNT_TYPE," +
                "        SIGNING_BANK," +
                "        ORGANIZATION_CODE," +
                "        ORGANIZATION_NAME," +
                "        BUSINESS_CODE," +
                "        BUSINESS_NAME," +
                "        PROJECT_CODE," +
                "        PROJECT_NAME," +
                "        BILL_MONEY," +
                "        CHANNEL_ID," +
                "        CHANNEL_NAME," +
                "        BILL_REASON," +
                "        effective_sign," +
                "        BILL_FROM," +
                "        PAY_STATU," +
                "        BILL_RESULT," +
                "        import_batchno," +
                "        freezing_money," +
                "        liquidation_batchNum," +
                "        transaction_date," +
                "        OLD_MONEY," +
                "        SETTLE_CHANNEL," +
                "        FIRST_LIQUI_DATE," +
                "        account_nature," +
                "        account_area," +
                "        row_index," +
                "        postal_sign," +
                "        ACCOUNT_BANKNAME," +
                "        MERCHANT_FROM," +
                "        POUNDAGE_MONEY," +
                "        EARNINGS_MONEY," +
                "        import_user_id," +
                "        import_time," +
                "        import_file_name," +
                "        TOPAY_STATU," +
                "        FROZEN_EXCEPTION," +
                "        BILL_STATUS," +
                "        settlment_batchNum," +
                "        RESPITE_STATUS," +
                "        PROFIT_STATUS," +
                "        PROFIT_ID," +
                "        phoneNumber," +
                "        PAY_RESPONSE_CODE," +
                "        REAL_PAY_STATUS," +
                "        NEW_CARD_NUMBER," +
                "        FILE_FROM_NAME," +
                "        FYJ_ID," +
                "        CLEAR_MODE," +
                "        PAY_APPLY_STATUS," +
                "        PAY_APPLY_NAME," +
                "        PAY_APPLY_TIME" +
                "        ) VALUES " +
                "        (?,?,?,?,?,?,?,?,?,?,?," +
                "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," +
                "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," +
                "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            conn = JDBCUtil.getQSConn();
            pstat = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            for(int i=0;i<list.size();i++){
                obj = list.get(i);
                pstat.setInt(1, obj.getId());
                pstat.setString(2, obj.getClearDate());
                pstat.setString(3, obj.getBepayBatch());
                pstat.setString(4, obj.getMerchantCode());
                pstat.setString(5, obj.getMerchantName());
                pstat.setString(6, obj.getAgentCode());
                pstat.setString(7, obj.getAgentName());
                pstat.setString(8, obj.getAccountName());
                pstat.setString(9, obj.getCardNumber());
                pstat.setString(10, obj.getBankName());
                pstat.setInt(11, obj.getAccountType());
                pstat.setString(12, obj.getSigingBank());
                pstat.setString(13, obj.getOrganizationCode());
                pstat.setString(14, obj.getOrganizationName());
                pstat.setString(15, obj.getBusinessCode());
                pstat.setString(16, obj.getBusinessName());
                pstat.setString(17, obj.getProjectCode());
                pstat.setString(18, obj.getProjectName());
                pstat.setBigDecimal(19, obj.getBillMoney());
                pstat.setInt(20, obj.getChannelId());
                pstat.setString(21, obj.getChannelName());
                pstat.setInt(22, obj.getBillReason());
                pstat.setInt(23, obj.getEffectiveSign());
                pstat.setInt(24, obj.getBillFrom());
                pstat.setInt(25, obj.getPayStatu());
                pstat.setInt(26, obj.getBillResult());
                pstat.setString(27, obj.getImportBatchNo());
                pstat.setBigDecimal(28, obj.getFreezingMoney());
                pstat.setString(29, obj.getLiquidationBatchNum());
                pstat.setString(30, obj.getTransactionDate());
                pstat.setBigDecimal(31, obj.getOldMoney());
                pstat.setInt(32, obj.getSettleChannel());
                pstat.setString(33, obj.getFirstLiquiDate());
                pstat.setInt(34, obj.getAccountNature());
                pstat.setString(35, obj.getAccountArea());
                pstat.setInt(36, obj.getRowIndex());
                pstat.setInt(37, obj.getPostalSign());
                pstat.setString(38, obj.getAccountBankName());
                pstat.setString(39, obj.getMerchantFrom());
                pstat.setBigDecimal(40, obj.getPoundageMoney());
                pstat.setBigDecimal(41, obj.getEarningsMoney());
                pstat.setInt(42, obj.getImportUserId());
                pstat.setString(43, obj.getImportTime());
                pstat.setString(44, obj.getImportFileName());
                pstat.setInt(45, obj.getTopayStatu());
                pstat.setBigDecimal(46, obj.getFrozenException());
                pstat.setInt(47, obj.getBillStatus());
                pstat.setString(48, obj.getSettlmentBatchNum());
                pstat.setInt(49, obj.getRespiteStatus());
                pstat.setInt(50, obj.getProfitStatus());
                pstat.setInt(51, obj.getProfitId());
                pstat.setString(52, obj.getPhoneNumber());
                pstat.setString(53, obj.getPayResponseCode());
                pstat.setInt(54, obj.getRealPayStatus());
                pstat.setString(55, obj.getNewCardNumber());
                pstat.setString(56, obj.getFileFromName());
                pstat.setInt(57, obj.getFyjId());
                pstat.setString(58, obj.getClearMode());
                pstat.setInt(59, obj.getPayApplyStatus());
                pstat.setString(60, obj.getPayApplyName());
                pstat.setString(61, obj.getPayApplyTime());
                pstat.addBatch();
                if (i%2000==0&&i>0) {
                    pstat.executeBatch();
                    conn.commit();
                    LogUtil.info(this.getClass(), "挂账表分批保存条数至："+i);
                }
            }
            pstat.executeBatch();
            conn.commit();
            long endTime = System.currentTimeMillis();
            LogUtil.info(this.getClass(), "挂账表分批保存条数至："+list.size()+"，耗时："+(endTime-startTime)/1000);
        } catch (Exception e) {
            e.printStackTrace();
            if(conn!=null){
                try {
                    conn.rollback();
                    LogUtil.info(this.getClass(), "挂账表分批次保存异常数据进行回滚！");
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            throw new RuntimeException(e.getMessage());
        } finally {
            JDBCUtil.getClose(conn, pstat, null);
        }
    }
}
